﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Data;

namespace Utils
{
    public class ExcelHelper
    {
        #region 第三方导出Excel
        public static void ExportDataSetToFile(string[] caption, string[] columns, DataSet ds, string fileName)
        {

            if (ds == null || ds.Tables[0].Rows.Count == 0) return;

            if (caption == null || columns == null || caption.Length != columns.Length)
            {
                columns = null;
            }
            //fileName = DateTime.Now.ToString("yyyyMMddHHmmss");
            HttpResponse resp;
            resp = HttpContext.Current.Response;

            #region 原始方法
            //resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            //resp.HeaderEncoding = System.Text.Encoding.GetEncoding("GB2312"); //!important
            //#region 临时不用
            ////string tempFileName = HttpUtility.UrlEncode(fileName, Encoding.UTF8);
            ////resp.AppendHeader("Content-Disposition", "attachment;filename=" + tempFileName + ".xls");
            ////resp.Charset = "GB2312";
            ////resp.ContentType = "Application/ms-excel"; 
            ////resp.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;";
            //#endregion
            #endregion

            #region wup 修改兼容浏览器
            if (HttpContext.Current.Request.Browser.Browser.ToUpper().Contains("SAFA"))
            {
                resp.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
                resp.HeaderEncoding = System.Text.Encoding.GetEncoding("utf-8"); //!important
            }
            else
            {
                resp.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
                resp.HeaderEncoding = System.Text.Encoding.GetEncoding("gb2312"); //!important
            }
            if (HttpContext.Current.Request.Browser.Browser.ToUpper().Contains("IE") && HttpContext.Current.Request.Browser.Version == "6.0")
            {
                resp.AddHeader("Content-Disposition", "attachment;filename=\"" + fileName + "\"");
                resp.Charset = "GB2312";
            }
            #endregion

            AppLibrary.WriteExcel.XlsDocument doc = new AppLibrary.WriteExcel.XlsDocument();
            doc.FileName = fileName;
            string SheetName = "sheet1";
            AppLibrary.WriteExcel.Worksheet sheet = doc.Workbook.Worksheets.Add(SheetName);
            AppLibrary.WriteExcel.Cells cells = sheet.Cells;

            AppLibrary.WriteExcel.XF xf = doc.NewXF();
            xf.Font.Height = 14 * 20;

            int r = 0;//计算行数
            #region 首行标题
            if (caption != null && caption.Length > 0)
            {
                r++;
                for (int i = 0; i < caption.Length; i++)
                {
                    cells.Add(r, (i + 1), caption[i], xf);
                }
            }
            else
            {
                r++;
                for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                {
                    cells.Add(r, (i + 1), ds.Tables[0].Columns[i].Caption.ToString(), xf);
                }
            }
            #endregion

            xf.Font.Height = 12 * 20;

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                r++;
                if (columns == null || columns.Length == 0)
                {
                    for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                    {
                        object value = dr[i].ToString();
                        if (ds.Tables[0].Columns[columns[i]].DataType == typeof(decimal) || ds.Tables[0].Columns[columns[i]].DataType == typeof(double))
                        {
                            try
                            {
                                double d = Convert.ToDouble(value);
                                value = d;
                            }
                            catch
                            {
                            }
                        }
                        cells.Add(r, (i + 1), value, xf);
                    }
                }
                else
                {
                    for (int i = 0; i < columns.Length; i++)
                    {
                        if (ds.Tables[0].Columns.Contains(columns[i]))
                        {
                            object value = dr[columns[i]].ToString();
                            //仅处理decimal、double数据类型，int\long类型的诸如dealerid=100008711不转型，否则会转成科学计数 edited by jiangzj 2014-7-30
                            if (ds.Tables[0].Columns[columns[i]].DataType == typeof(decimal) || ds.Tables[0].Columns[columns[i]].DataType == typeof(double))
                            {
                                try
                                {
                                    double d = Convert.ToDouble(value);
                                    value = d;
                                }
                                catch
                                {
                                }
                            }
                            cells.Add(r, (i + 1), value, xf);
                        }
                        else
                        {
                            cells.Add(r, (i + 1), "");
                        }
                    }
                }
            }

            doc.Send();
            resp.Flush();
            resp.End();
        }
        #endregion
    }
}
